package com.epoint.bookmanagement.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.epoint.bookmanagement.bizlogic.borrowinfo.domain.BorrowInfo;
import com.epoint.bookmanagement.utils.JDBCUtil;

import cn.hutool.core.date.DateUnit;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.tokenizer.Result;
import cn.hutool.http.useragent.OS;

public class BorrowInfoDao {

	/**
	 * 新增借阅信息
	 * @param borrowInfo 解约信息
	 * @return
	 */
	public int addBorrowInfo(BorrowInfo borrowInfo) {
		Connection con = JDBCUtil.getConnection();
		String sql = "insert into borrowinfo (borrowid, bookid, borrower, phone, borrowtime, returntime) values (?,?,?,?,?,?)";
		PreparedStatement ps = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, borrowInfo.getBorrowid());
			ps.setString(2, borrowInfo.getBookid());
			ps.setString(3, borrowInfo.getBorrower());
			ps.setString(4, borrowInfo.getPhone());
			ps.setObject(5, borrowInfo.getBorrowtime());
			ps.setObject(6, borrowInfo.getReturntime());
			System.err.println("addBorrowInfo-->sql-->" + ps);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		return i;
	}
	
	/**
	 * 删除单条借阅信息
	 * @param borrowId id
	 * @return
	 */
	public int deleteBorrowInfoByBorrowId(String borrowId) {
		Connection con = JDBCUtil.getConnection();
		String sql = "delete from borrowinfo where borrowid = ?";
		PreparedStatement ps = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, borrowId);
			System.err.println("deleteBorrowInfoByBorrowId-->sql-->" + ps);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		return i;
	}
	
	/**
	 * 修改借阅信息
	 * @param borrowInfo 借阅信息
	 * @return
	 */
	public int updateBorrowInfo(BorrowInfo borrowInfo) {
		Connection con = JDBCUtil.getConnection();
		String sql = "update borrowinfo set bookid = ? , borrower = ? , phone = ? , borrowtime = ? , returntime = ?  where borrowid = ?";
		PreparedStatement ps = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, borrowInfo.getBookid());
			ps.setString(2, borrowInfo.getBorrower());
			ps.setString(3, borrowInfo.getPhone());
			ps.setObject(4, borrowInfo.getBorrowtime());
			ps.setObject(5, borrowInfo.getReturntime());
			ps.setString(6, borrowInfo.getBorrowid());
			System.err.println("updateBorrowInfo-->sql-->" + ps);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		return i;
	}
	
	/**
	 * 查询借阅信息表中的数据条数
	 * @return
	 */
	public int findLength() {
		Connection con = JDBCUtil.getConnection();
		String sql = "select count(*) from borrowinfo";
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			System.err.println("findLength-->sql-->" + ps);
			rs = ps.executeQuery();
			while(rs.next()) {
				i = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return i;
	}
	
	/**
	 * 根据借阅编号查询借阅实体
	 * @param borrowId id
	 * @return
	 */
	public BorrowInfo queryBorrowInfoByBorrowId(String borrowId) {
		Connection con = JDBCUtil.getConnection();
		String sql = "select * from borrowinfo where borrowid = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		BorrowInfo borrowInfo = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, borrowId);
			System.err.println("queryBorrowInfoByBorrowId-->sql-->" + ps);
			rs = ps.executeQuery();
			while(rs.next()) {
				borrowInfo = new BorrowInfo();
				borrowInfo.setBorrowid(rs.getString("borrowid"));
				borrowInfo.setBookid(rs.getString("bookid"));
				borrowInfo.setBorrower(rs.getString("borrower"));
				borrowInfo.setPhone(rs.getString("phone"));
				borrowInfo.setBorrowtime(rs.getTimestamp("borrowtime"));
				borrowInfo.setReturntime(rs.getTimestamp("returntime"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return borrowInfo;
	}
	
	/**
	 * 查询列表数据，带有排序字段和排序方向
	 * @param pageIndex 页号
	 * @param pageSize 页面大小
	 * @param sortField 排序字段
	 * @param sortOrder 排序方向
	 * @return 结果
	 */
	public List<BorrowInfo> queryBorrowInfo(Integer pageIndex, Integer pageSize, String sortField, String sortOrder) {
		Connection con = JDBCUtil.getConnection();
		// 处理sql
		StringBuffer sql = new StringBuffer();
		sql.append("select * from borrowinfo where 1=1 ");
		if (StrUtil.isNotEmpty(sortField)) {
			sql.append(" order by ")
				.append(sortField).append("  ");
			
			if (StrUtil.isNotEmpty(sortOrder)) {
				sql.append(sortOrder);
			}
		}
		
		// 可选分页
		if (pageIndex != null && pageSize != null) {
			sql.append(" limit ?, ? ");
		}
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		BorrowInfo borrowInfo = null;
		List<BorrowInfo> resultList = new ArrayList<>(pageSize == null ? 10 : pageSize);
		
		try {
			ps = con.prepareStatement(sql.toString());
			// 可选分页
			if (pageIndex != null && pageSize != null) {
				// 设置值
				int indexNum = pageIndex * pageSize;
				ps.setInt(1, indexNum);
				ps.setInt(2, pageSize);
			}
			
			System.err.println("queryBorrowInfo-->sql-->" + ps);
			rs = ps.executeQuery();
			// 封装结果
			while(rs.next()) {
				borrowInfo = new BorrowInfo();
				borrowInfo.setBorrowid(rs.getString("borrowid"));
				borrowInfo.setBookid(rs.getString("bookid"));
				borrowInfo.setBorrower(rs.getString("borrower"));
				borrowInfo.setPhone(rs.getString("phone"));
				borrowInfo.setBorrowtime(rs.getTimestamp("borrowtime"));
				borrowInfo.setReturntime(rs.getTimestamp("returntime"));
				resultList.add(borrowInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return  resultList;
	}
	
	/**
	 * 生成borrowId
	 * @return id
	 */
	public String generalBorrowId() {
		Connection con = JDBCUtil.getConnection();
		String sql = "select concat('Borrow', year(now()), lpad(right(max(right(borrowid, 8)),4) + 1, 4, 0))  from borrowinfo";
		PreparedStatement ps = null;
		ResultSet rs = null;
		String borrowId = null;
		try {
			ps = con.prepareStatement(sql);
			System.err.println("generalBorrowId-->sql-->" + ps);
			rs = ps.executeQuery();
			while(rs.next()) {
				borrowId = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return borrowId;
	}

	/**
	 * 新增借阅信息
	 * @param borrowInfo 借阅信息
	 * @return
	 */
	public int addBorrowInfoAndsubRemain(BorrowInfo borrowInfo) {
		Connection con  = JDBCUtil.getConnection();
		String sql1 = "insert into borrowinfo (borrowid, bookid, borrower, phone, borrowtime, returntime) values (?,?,?,?,?,?)";
		String sql2 = "update bookinfo set remain = (remain - 1) where bookid = ?";
		PreparedStatement ps = null;
		int result = 0, i = 0, j = 0;
		try {
			// 取消自动提交事务
			con.setAutoCommit(false);
			ps = con.prepareStatement(sql1);
			ps.setString(1, borrowInfo.getBorrowid());
			ps.setString(2, borrowInfo.getBookid());
			ps.setString(3, borrowInfo.getBorrower());
			ps.setString(4, borrowInfo.getPhone());
			ps.setObject(5, borrowInfo.getBorrowtime());
			ps.setObject(6, borrowInfo.getReturntime());
			System.err.println("addBorrowInfoAndsubRemain-->sql1-->" + ps);
			i = ps.executeUpdate();
			if (i > 0) {
				ps.close();
				ps = con.prepareStatement(sql2);
				ps.setString(1, borrowInfo.getBookid());
				System.err.println("addBorrowInfoAndsubRemain-->sql2-->" + ps);
				j = ps.executeUpdate();
			}
			// 判读事务提交
			if (i > 0 && j > 0) {
				result = 1;
				// 手动提交事务
				con.commit();
			} else {
				// 事务回滚
				con.rollback(); 
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		return result;
	}

	/**
	 * 归还图书
	 * @param borrowId 借阅id
	 * @param bookId 图书id
	 * @return
	 */
	public int returnBook(String borrowId, String bookId) {
		Connection con  = JDBCUtil.getConnection();
		String sql1 = "update borrowinfo set returntime = ? where borrowid = ?";
		String sql2 = "update bookinfo set remain = (remain + 1) where bookid = ?";
		PreparedStatement ps = null;
		int result = 0, i = 0, j = 0;
		
		try {
			// 取消自动提交事务
			con.setAutoCommit(false);
			ps = con.prepareStatement(sql1);
			ps.setObject(1, new Date());
			ps.setString(2, borrowId);
			System.err.println("returnBook-->sql1-->" + ps);
			i = ps.executeUpdate();
			if (i > 0) {
				ps.close();
				ps = con.prepareStatement(sql2);
				ps.setString(1, bookId);
				System.err.println("returnBook-->sql2-->" + ps);
				j = ps.executeUpdate();
			}
			// 判读事务提交
			if (i > 0 && j > 0) {
				result = 1;
				// 手动提交事务
				con.commit();
			} else {
				// 事务回滚
				con.rollback(); 
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		return result;
	}
}
